package com.hdu.dwh.service.impl;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.hdu.dwh.pojos.QueryResult;
import com.hdu.dwh.service.TrinoQueryService;
import com.hdu.dwh.utils.TrinoQueryUtils;

@Service
public class TrinoQueryServiceImpl implements TrinoQueryService {
    @Autowired
    private TrinoQueryUtils trinoQueryUtils;

    @Override
    public QueryResult executeQuery(String sql) throws Exception {
        Connection connection;
        long startTime = System.currentTimeMillis();
        try {
            connection = trinoQueryUtils.createTrinoConnection();
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            
            long endTime = System.currentTimeMillis();

            
            return this.ConvertResultSetToQueryResult(resultSet, startTime, endTime);
        } catch (SQLException e) {
            throw e;
        }
    }



    @Override
    public QueryResult ConvertResultSetToQueryResult(ResultSet resultSet, long startTime, long endTime) throws Exception {
        List<String> columnNames = new ArrayList<>();
        int columnCount = resultSet.getMetaData().getColumnCount();
        for (int i = 1; i <= columnCount; i++) {
            columnNames.add(resultSet.getMetaData().getColumnName(i));
        }

        List<List<String>> rows = new ArrayList<>();
        while (resultSet.next()) {
            List<String> row = new ArrayList<>();
            for (int i = 1; i <= columnCount; i++) {
                row.add(resultSet.getString(i));
            }
            rows.add(row);
        }

        QueryResult queryResult = new QueryResult(columnNames, rows, endTime - startTime);
        return queryResult;
    }

    @Override
    public QueryResult queryWithDatabaseAndTable(String database, String table, Integer limit, Integer offset) throws Exception {
        String sql = String.format("SELECT * FROM \"%s\".%s OFFSET %d LIMIT %d", database, table, offset, limit);
        try {
            return this.executeQuery(sql);
        } catch (Exception e) {
            throw e;
        }
    }

    @Override
    public String getColumnType(String databaseName, String tableName, String columnName) throws Exception {
        Connection connection = trinoQueryUtils.createTrinoConnection();
        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet columns = metaData.getColumns(trinoQueryUtils.getDatawarehouse(), databaseName, tableName, columnName);
        if (columns.next()) {
            return columns.getString("TYPE_NAME").toUpperCase();
        } else {
            throw new Exception(String.format("Column %s not found in table %s.%s", columnName, databaseName, tableName));
        }
    }



    @Override
    public Set<String> queryDimensionValues(String databaseName, String tableName, String dimension) throws Exception {
        Connection connection = trinoQueryUtils.createTrinoConnection();
        String sql = String.format("SELECT DISTINCT \"%s\" FROM \"%s\".%s", dimension, databaseName, tableName);
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        Set<String> values = new HashSet<>();
        while (resultSet.next()) {
            values.add(resultSet.getString(1));
        }
        return values;
    }
    
}
